package com.csi.okr.common.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SQLiteUtil {
	
	private static Connection connection;
	
	
	/**
	 * 执行SQL语句  返回List集合
	 * @param sql
	 * @return
	 */
	public static List<Map<String, Object>> select(String sql) {
		
		try {
			
			System.out.println("===SQL:===" + sql);
			
			SQLiteConn conn = new SQLiteConn();
			connection = conn.getConnection();
			connection.setAutoCommit(false);
			Statement stmt = connection.createStatement();
			
			//先清空表数据
			ResultSet rs = stmt.executeQuery(sql);
			
			
			List<Map<String, Object>> data =  parseResutSet(rs);
			
			stmt.close();
			connection.commit();
			connection.close();
			
			return data;
			
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			connectionRollback(connection);
			return null;
		}
	}

	/**
	 * 清空表并重新插入数据
	 * @param table 表名
	 * @param params 参数集合
	 */
	public static void transToInsert(String table, List<?> params) {
		
		try {
			
			SQLiteConn conn = new SQLiteConn();
			connection = conn.getConnection();
			connection.setAutoCommit(false);
			Statement stmt = connection.createStatement();
			
			//先清空表数据
			stmt.executeUpdate("delete from " + table + ";");
			
			// 遍历集合
			for (Object obj : params) {
				// 把对象转换成Map
				Map<String, Object> map = BeanUtil.toMap(obj);
				//执行插入语句               //把Map拼接为sql
				stmt.executeUpdate(joinParams(table, map));
			}

			stmt.close();
			connection.commit();
			connection.close();
			
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			connectionRollback(connection);
		}
	}

	
	/**
	 * Map To SQL
	 * @param table 表名
	 * @return SQL
	 */
	private static String joinParams(String table, Map<String, Object> data) {

		String sql = "insert into " + table + "(" ;
		
		// 遍历Map 取到每个Key
		for (Map.Entry<String, Object> entry : data.entrySet()) {

			sql += entry.getKey() + ",";
		}
		
		// 去除最后一个逗号
		sql = sql.substring(0, sql.length() - 1);
		// 加上反括号
		sql += ") values(";
		
		// 遍历Map 取到每个Value
		for (Map.Entry<String, Object> entry : data.entrySet()) {

			sql += "'" + entry.getValue() + "',";
		}
		// 去除最后一个逗号
		sql = sql.substring(0, sql.length() - 1);
		// 加上反括号
		sql += ");";

		System.out.println(sql);

		return sql;

	}
	
	
	/**
	 * 格式化结果集 TO Map
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> parseResutSet(ResultSet rs) throws SQLException {

		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();

		ResultSetMetaData md = rs.getMetaData();

		// 获得列数
		int columnCount = md.getColumnCount();
		
		while (rs.next()) {

			Map<String, Object> rowData = new HashMap<String, Object>();
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			
			data.add(rowData);

		}

		return data;
	}

	/**
	 * 异常回滚
	 * @param connection
	 */
	private static void connectionRollback(Connection connection) {
		try {
			connection.rollback();
		} catch (SQLException e) {
			System.out.println("异常时回滚错误 : " + e.getLocalizedMessage());
		}
	}
}
